/****** Object:  Table [dbo].[Units]    Script Date: 01/15/2012 21:43:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Units](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[INV_NO] [nvarchar](20) NULL,
	[SN] [nvarchar](50) NULL,
	[SPR_ID] [int] NOT NULL,
	[SPR_Type] [varchar](20) NOT NULL,
	[FILIAL_ID] [int] NULL,
	[OTDEL_ID] [int] NULL,
	[KAB_ID] [int] NULL,
	[Price] [decimal](18, 2) NULL,
 CONSTRAINT [PK_Units] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Units]  WITH CHECK ADD  CONSTRAINT [FK_Units_SPR_KAB] FOREIGN KEY([KAB_ID])
REFERENCES [dbo].[SPR_KAB] ([ID])
GO

ALTER TABLE [dbo].[Units] CHECK CONSTRAINT [FK_Units_SPR_KAB]
GO

ALTER TABLE [dbo].[Units]  WITH CHECK ADD  CONSTRAINT [FK_Units_SPR_OTD_FILIAL] FOREIGN KEY([OTDEL_ID])
REFERENCES [dbo].[SPR_OTD_FILIAL] ([ID])
GO

ALTER TABLE [dbo].[Units] CHECK CONSTRAINT [FK_Units_SPR_OTD_FILIAL]
GO

ALTER TABLE [dbo].[Units]  WITH CHECK ADD  CONSTRAINT [FK_Units_SPR_FILIAL] FOREIGN KEY([FILIAL_ID])
REFERENCES [dbo].[SPR_FILIAL] ([ID])
GO

ALTER TABLE [dbo].[Units] CHECK CONSTRAINT [FK_Units_SPR_FILIAL]
GO

ALTER TABLE SPR_FILIAL ADD PRIMARY KEY (ID)
ALTER TABLE SPR_KAB ADD PRIMARY KEY (ID)
GO

-- Add references into spr_kab table - start --
ALTER TABLE [SPR_KAB] ADD F_ID INT NULL
ALTER TABLE [SPR_KAB] ADD O_ID INT NULL
GO

ALTER TABLE [dbo].[SPR_KAB]  WITH CHECK ADD  CONSTRAINT [FK_SPR_KAB_SPR_FILIAL] FOREIGN KEY([F_ID])
REFERENCES [dbo].[SPR_FILIAL] ([ID])
GO

ALTER TABLE [dbo].[SPR_KAB] CHECK CONSTRAINT [FK_SPR_KAB_SPR_FILIAL]
GO

ALTER TABLE [dbo].[SPR_KAB]  WITH CHECK ADD  CONSTRAINT [FK_SPR_KAB_SPR_OTD_FILIAL] FOREIGN KEY([O_ID])
REFERENCES [dbo].[SPR_OTD_FILIAL] ([ID])
GO

ALTER TABLE [dbo].[SPR_KAB] CHECK CONSTRAINT [FK_SPR_KAB_SPR_OTD_FILIAL]
GO

UPDATE SPR_KAB SET F_ID = f.ID FROM SPR_KAB k
INNER JOIN SPR_FILIAL f ON k.N_F = f.FILIAL

UPDATE SPR_KAB SET O_ID = o.ID FROM SPR_KAB k
INNER JOIN SPR_OTD_FILIAL o ON k.N_M = o.N_Otd

ALTER TABLE SPR_KAB ALTER COLUMN O_ID INT NOT NULL
ALTER TABLE SPR_KAB ALTER COLUMN F_ID INT NOT NULL
-- Add references into spr_kab table - ends --

-- Add references into spr_otb_filial table - start --
ALTER TABLE [SPR_OTD_FILIAL] ADD F_ID INT NULL
GO

ALTER TABLE [dbo].[SPR_OTD_FILIAL]  WITH CHECK ADD  CONSTRAINT [FK_SPR_OTD_FILIAL_SPR_FILIAL] FOREIGN KEY([F_ID])
REFERENCES [dbo].[SPR_FILIAL] ([ID])
GO

ALTER TABLE [dbo].[SPR_OaTD_FILIAL] CHECK CONSTRAINT [FK_SPR_OTD_FILIAL_SPR_FILIAL]
GO

UPDATE SPR_OTD_FILIAL SET F_ID = f.ID FROM SPR_OTD_FILIAL o
INNER JOIN SPR_FILIAL f ON o.Filial = f.FILIAL

ALTER TABLE SPR_OTD_FILIAL ALTER COLUMN F_ID INT NOT NULL
-- Add references into spr_otb_filial table - end --

-- Add references into spr_bp table - start --
ALTER TABLE [dbo].[SPR_BP]  WITH CHECK ADD  CONSTRAINT [FK_SPR_BP_SPR_PROIZV] FOREIGN KEY([Proizv])
REFERENCES [dbo].[SPR_PROIZV] ([ID])
GO

ALTER TABLE [dbo].[SPR_BP] CHECK CONSTRAINT [FK_SPR_BP_SPR_PROIZV]
GO
-- Add references into spr_bp table - end --

-- Add references into spr_case table - start --
ALTER TABLE [dbo].[SPR_CASE]  WITH CHECK ADD  CONSTRAINT [FK_SPR_CASE_SPR_PROIZV] FOREIGN KEY([Proizv])
REFERENCES [dbo].[SPR_PROIZV] ([ID])
GO

ALTER TABLE [dbo].[SPR_CASE] CHECK CONSTRAINT [FK_SPR_CASE_SPR_PROIZV]
GO
-- Add references into spr_case table - end --

-- Add references into spr_creader table - start --
ALTER TABLE [dbo].[SPR_CREADER]  WITH CHECK ADD  CONSTRAINT [FK_SPR_CREADER_SPR_PROIZV] FOREIGN KEY([Proizv])
REFERENCES [dbo].[SPR_PROIZV] ([ID])
GO

ALTER TABLE [dbo].[SPR_CREADER] CHECK CONSTRAINT [FK_SPR_CREADER_SPR_PROIZV]
GO
-- Add references into spr_creader table - end --

-- Add references into spr_oth_dev table - start --
ALTER TABLE [dbo].[SPR_OTH_DEV]  WITH CHECK ADD  CONSTRAINT [FK_SPR_OTH_DEV_SPR_PROIZV] FOREIGN KEY([Proizv])
REFERENCES [dbo].[SPR_PROIZV] ([ID])
GO

ALTER TABLE [dbo].[SPR_OTH_DEV] CHECK CONSTRAINT [FK_SPR_OTH_DEV_SPR_PROIZV]
GO
-- Add references into spr_oth_dev table - end --
